"""
交易后的检查脚本
获取逻辑：
根据endTime参数，循环向前递推，直到没有订单返回。则退出。
注意点：
1binance end point 有一次最多1000笔的限制，所以，递推间隔默认为6天，（每次保留一天的冗余）。如果6天内的成交超过1000笔，则需要修改递推的频率
"""

import sys
import ccxt
import time
import json
import pymysql
from Function import retry


@retry('get trades', 3)
def get_trades(EXCHANGE, endTime=None):
    r = int(time.time() * 1000)

    parms = {
        "symbol": "ETHUSDT",
        "timestamp": r,
        "limit": 1000,
        # "endTime": 1665643930000,
        "endTime": endTime if endTime else r  # 没有传参数就使用当前时间
    }
    res = EXCHANGE.fapiPrivateGetUserTrades(parms)
    if len(res) == 1000:
        raise Exception('查询结果可能超过接口单次查询上限，请检查！')
    # for i in res:
    #     print(i)
    return res


@retry('get cash flow', 3)
def get_cash_flow(EXCHANGE, endTime=None):
    r = int(time.time() * 1000)

    parms = {
        "timestamp": r,
        "limit": 1000,
        "endTime": endTime if endTime else r  # 没有传参数就使用当前时间
    }
    res = EXCHANGE.fapiPrivateGetIncome(parms)
    if len(res) == 1000:
        raise Exception('查询结果可能超过接口单次查询上限，请检查！')
    # for i in res:
    #     print(i)
    return res


if __name__ == "__main__":
    # 此处使用的是测试的apikey和secret
    with open('../config/crypto_check_filled_config.json') as f:
        cfg = json.load(f)
    BINANCE_CONFIG = cfg['exchange']

    EXCHANGE = ccxt.binance(BINANCE_CONFIG)
    # r = EXCHANGE.parse8601('2022-10-10 19:22:00')

    try:
        MySQlDB = pymysql.connect(host=cfg["Mysql_DB"]['MySQL_IP'], user=cfg["Mysql_DB"]['MySQL_User'],
                                  password=cfg["Mysql_DB"]['MySQL_PassWord'], database=cfg["Mysql_DB"]['DB'])
    except Exception as e:
        print("连接mysql数据库失败:%s" % e)
        sys.exit(1)
    else:
        MySqlCR = MySQlDB.cursor()
    # region 记录成交数据=================================================================================================
    print("获取交易记录!")
    SQL = f"""REPLACE INTO {cfg["Mysql_DB"]["DB"]}.{cfg["Mysql_DB"]['table']}(buyer, commission, commissionAsset, id,maker, orderId, price,  qty, quoteQty, realizedPnl, side, positionSide, symbol, time)VALUES """
    tp = MySqlCR.execute(f"SELECT time FROM {cfg['Mysql_DB']['table']} order by time desc limit 1")
    last_ord_time = MySqlCR.fetchall()
    if last_ord_time:
        last_ord_time = last_ord_time[0][0]  # 获取数据库中已有的订单的最大值
    else:
        last_ord_time = 0  # 这种情况说明数据库是空的，直接全部获取，等到交易所没有数据才退出

    t = int(time.time() * 1000)
    while True:
        res = get_trades(EXCHANGE, t)
        if len(res) == 0:  # 没有订单了，就退出
            break
        for o in res:
            SQL += f"""({o['buyer']}, {o['commission']}, '{o['commissionAsset']}', {o['id']}, {o['maker']}, {o['orderId']}, {o['price']},  {o['qty']}, {o['quoteQty']}, {o['realizedPnl']}, '{o['side']}', '{o['positionSide']}', '{o['symbol']}', {o['time']}),"""
        t -= 6 * 60 * 60 * 24 * 1000  # 乘上六天的毫秒数
        if t < last_ord_time:  # 本次获取到的数据的时间已经小于数据库中最大的数据了，退出～
            break

        time.sleep(1)
    SQL = SQL.strip(',')
    try:
        MySqlCR.execute(SQL)
        MySQlDB.commit()
    except Exception as e:
        print("执行成交SQL失败！请检查！")
        print(e)
        print(SQL)
        exit(1)
    # endregion

    # region 记录资金数据=================================================================================================
    print("获取资金流水！")
    SQL = f"""REPLACE INTO {cfg["Mysql_DB"]["DB"]}.{cfg['Mysql_DB']['table_cash_flow']}(id, symbol, incomeType, income, asset, info, time, tranId, tradeId)VALUES """
    tp = MySqlCR.execute(f"SELECT time FROM {cfg['Mysql_DB']['table_cash_flow']} order by time desc limit 1")
    last_ord_time = MySqlCR.fetchall()
    if last_ord_time:
        last_ord_time = last_ord_time[0][0]  # 获取数据库中已有的订单的最大值
    else:
        last_ord_time = 0  # 这种情况说明数据库是空的，直接全部获取，等到交易所没有数据才退出

    t = int(time.time() * 1000)
    while True:
        res = get_cash_flow(EXCHANGE, t)
        if len(res) == 0:  # 没有订单了，就退出
            break
        for o in res:
            SQL += f"""('{o['incomeType']+ '_' + o['tranId']}', '{o['symbol']}','{o['incomeType']}',{o['income']},'{o['asset']}','{o['info']}', {o['time']}, {o['tranId']}, '{o['tradeId']}'),"""
        t -= 6 * 60 * 60 * 24 * 1000  # 乘上六天的毫秒数
        if t < last_ord_time:  # 本次获取到的数据的时间已经小于数据库中最大的数据了，退出～
            break

        time.sleep(1)
    SQL = SQL.strip(',')
    try:
        MySqlCR.execute(SQL)
        MySQlDB.commit()
    except Exception as e:
        print("执行SQL失败！请检查！")
        print(e)
        print(SQL)
        exit(1)
    # endregion
